#============================================
#知识点(大多了解)
# 1.学生表Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
# 2.课程表Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
# 3.教师表Teacher(t_id,t_name) –教师编号,教师姓名
# 4.成绩表Score(s_id,c_id,s_score) –学生编号,课程编号,分数测试数据

# --建表
# --学生表
# CREATE TABLE `Student`(
#     `s_id` VARCHAR(20),
#     `s_name` VARCHAR(20) NOT NULL DEFAULT '',
#     `s_birth` VARCHAR(20) NOT NULL DEFAULT '',
#     `s_sex` VARCHAR(10) NOT NULL DEFAULT '',
#     PRIMARY KEY(`s_id`)
# );
# --课程表
# CREATE TABLE `Course`(
#     `c_id`  VARCHAR(20),
#     `c_name` VARCHAR(20) NOT NULL DEFAULT '',
#     `t_id` VARCHAR(20) NOT NULL,
#     PRIMARY KEY(`c_id`)
# );
# --教师表
# CREATE TABLE `Teacher`(
#     `t_id` VARCHAR(20),
#     `t_name` VARCHAR(20) NOT NULL DEFAULT '',
#     PRIMARY KEY(`t_id`)
# );
# --成绩表
# CREATE TABLE `Score`(
#     `s_id` VARCHAR(20),
#     `c_id`  VARCHAR(20),
#     `s_score` INT(3),
#     PRIMARY KEY(`s_id`,`c_id`)
# );
# --插入学生表测试数据
# insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
# insert into Student values('02' , '钱电' , '1990-12-21' , '男');
# insert into Student values('03' , '孙风' , '1990-05-20' , '男');
# insert into Student values('04' , '李云' , '1990-08-06' , '男');
# insert into Student values('05' , '周梅' , '1991-12-01' , '女');
# insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
# insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
# insert into Student values('08' , '王菊' , '1990-01-20' , '女');
# --课程表测试数据
# insert into Course values('01' , '语文' , '02');
# insert into Course values('02' , '数学' , '01');
# insert into Course values('03' , '英语' , '03');

# --教师表测试数据
# insert into Teacher values('01' , '张三');
# insert into Teacher values('02' , '李四');
# insert into Teacher values('03' , '王五');

# --成绩表测试数据
# insert into Score values('01' , '01' , 80);
# insert into Score values('01' , '02' , 90);
# insert into Score values('01' , '03' , 99);
# insert into Score values('02' , '01' , 70);
# insert into Score values('02' , '02' , 60);
# insert into Score values('02' , '03' , 80);
# insert into Score values('03' , '01' , 80);
# insert into Score values('03' , '02' , 80);
# insert into Score values('03' , '03' , 80);
# insert into Score values('04' , '01' , 50);
# insert into Score values('04' , '02' , 30);
# insert into Score values('04' , '03' , 20);
# insert into Score values('05' , '01' , 76);
# insert into Score values('05' , '02' , 87);
# insert into Score values('06' , '01' , 31);
# insert into Score values('06' , '03' , 34);
# insert into Score values('07' , '02' , 89);
# insert into Score values('07' , '03' , 98);
#============================================
# ```sql
# -- 创建学生表, 课程表, 成绩表
# -- 1. 学生表: 学号, 姓名, 性别, 住址等...
# -- 2. 课程表: 课程编号, 课程名称, 授课教师等...
# -- 3. 学生课程-成绩表:  成绩表编号, 学号, 课程编号, 成绩
# create table stu(
# 	sid int primary key auto_increment,
# 	sname varchar(50) not null, 
# 	gender int(1),
# 	address varchar(255)
# );

# create table course(
# 	cid int primary key auto_increment,
# 	cname varchar(50) not null, 
# 	teacher varchar(50)
# );

# create table sc(
# 	sc_id int primary key auto_increment,
# 	s_id int, 
# 	c_id int,
# 	score int,
# 	CONSTRAINT FK_SC_STU_S_ID FOREIGN key(s_id) REFERENCES stu(sid),
# 	CONSTRAINT FK_SC_COURSE_C_ID FOREIGN key(c_id) REFERENCES course(cid)
# );

# ```



# #### 4.5.1 子查询

# ​	在where语句中可以进行另外的一个查询. 

# ​	例如, 查询选择了"编程"这门课的学生

# ```sql
# -- 查询选择了"编程"这门课的学生
# -- 先查询编程课程的编号
# select cid from course where cname = '编程';
# -- 根据cid可以去sc表查询出学生的id
# select s_id from sc where c_id = 2;
# -- 根据学生ID查询学生信息
# select * from stu where sid in (1,2,3,4,5,6);

# -- 把上面的sql穿起来 
# select * from stu where sid in (
#     select s_id from sc where c_id in (
#         select cid from course where cname = '编程'
#     )
# );

# -- 查询课程名称为“编程”，且分数低于60的学生姓名和分数
# select stu.sname, sc.score from stu, sc where stu.sid = sc.s_id and sc.score < 60 and sc.c_id in (
# 	select cid from course where cname = '编程'
# )

# ```



# #### 4.5.2 关联查询

# 关联查询就是把多个表格通过join的方式合并在一起. 然后进行条件检索. 

# 语法规则:

# ```sql
# select ... from A xxx join B on A.字段1 = b.字段2

# 表示:  A表和B表连接. 通过A表的字段1和b表的字段2进行连接. 通常on后面的都是主外键关系
# ```



# ##### 4.5.2.1 inner jion

# ```sql
# -- 查询每门课程被选修的学生数
# -- count(*)
# -- group by cid

# select c.cid,c.cname, count(*) from sc inner join course c on sc.c_id = c.cid group by c.cid, c.cname
# ```



# ##### 4.5.2.2 left join

# ```sql
# -- 查询所有学生的选课情况
# select s.sname, c.cname from stu s left join sc on s.sid= sc.s_id left join course c on sc.c_id = c.cid

# -- 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
# -- score > 70 sc
# -- sname student
# -- cname course
# select s.sname, c.cname, sc.score from stu s inner join sc on s.sid = sc.s_id inner join course c on sc.c_id = c.cid
# where sc.score > 70
# ```

